package cn.dy.workatten.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.dy.base.framework.db.DBConnectPool;
import cn.dy.base.framework.db.DBUtil;
import cn.dy.base.framework.db.util.DBExecuteUtil;
import cn.dy.workatten.pojo.ContactInfo;
import cn.dy.workatten.pojo.MemberInfo;

public class ContactInfoDao {
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedJdbcTemplate;
	private DBExecuteUtil db;

	public ContactInfoDao() {
		this.db = new DBExecuteUtil();
		this.dataSource = DBConnectPool.create().getDataSource();
		if (this.jdbcTemplate == null) {
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		}
		if (this.namedJdbcTemplate == null) {
			this.namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
		}
	}

	public ContactInfo getContact(String name) throws Exception {
		Map params = new HashMap();
		ContactInfo contact = null;
		String sql = "select e.* from ecc_contact_info e where e.name=:name";
		params.put("name", name);
		return contact = (ContactInfo) this.db.queryObject(sql, params, ContactInfo.class);
	}

	public int createContactInfo(ContactInfo contact) throws Exception {
		return this.db.insert(contact, "ecc_contact_info");
	}

	public int updContact(ContactInfo contact) throws Exception {
		return this.db.update(contact, "ecc_contact_info", Integer.valueOf(contact.getId()));
	}

	public int delContact(int id) throws Exception {
		Map params = new HashMap();
		String sql = "delete from ecc_contact_info where id=:id";
		params.put("id", Integer.valueOf(id));
		return this.db.executeSql(sql, params);
	}

	public int getCountById(int id) {
		Map params = new HashMap();
		String sql = "SELECT COUNT(1) FROM ecc_contact_info eci,ecc_contact_holder ech,ecc_contact_group ecg WHERE eci.ID=ech.CONTACT_ID AND  ech.GROUP_ID=ecg.ID AND  ech.GROUP_ID=:id";
		params.put("id", Integer.valueOf(id));
		return ((Integer) this.db.queryObject(sql, params, Integer.class)).intValue();
	}

	public int getUniqueId() {
		return DBUtil.getSequence(new JdbcTemplate(this.dataSource), "seq_ecc_contact_info");
	}

	public int getContactIdByName(String name) {
		Map params = new HashMap();
		String sql = "SELECT ID FROM ecc_contact_info WHERE NAME=:name";
		params.put("name", name);
		return ((Integer) this.db.queryObject(sql, params, Integer.class)).intValue();
	}
	
	
	/**
	 * 查询某个部门的所有员工列表
	 * 
	 * @param dept_id
	 * @return
	 */
	public List<MemberInfo> getMemberListByDept(long dept_id, String query, int pageNum, int pageSize) {
		DBExecuteUtil db = new DBExecuteUtil();
		StringBuffer sbSql = new StringBuffer("select a.ID AS id, a.NAME AS name, case when a.GENDER=1 then '男' else '女' end gender,"
	    		+ " a.IDCARD idcard,a.`CODE` policecode,b.`NAME` dept, a.JOINTIME jointime, c.GROUP_ID AS deptId  from  ecc_contact_info a "
	    		+ "LEFT join ecc_contact_holder c on  a.ID=c.CONTACT_ID LEFT JOIN ecc_contact_group b ON c.GROUP_ID=b.ID where a.STS = 1 ");

		Map<String, Object> params = new HashMap();
		if(dept_id != 0) {
			sbSql.append(" and c.group_id=:dept_id ");
		}
		
		params.put("dept_id", Long.valueOf(dept_id));
		if (StringUtils.isNotEmpty(query)) {
	        sbSql.append(" and a.NAME like :query_member or a.IDCARD like :query_member or a.CODE like :query_member");
	        params.put("query_member", "%" + query + "%");
	    }
		sbSql.append(" order by a.staff_id ASC");
		
		if(pageNum != 0 && pageSize != 0) {
			return this.namedJdbcTemplate.query(DBUtil.getPagingSql(sbSql.toString()),
					DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(MemberInfo.class));
		} else {
			return db.queryList(sbSql.toString(), params, MemberInfo.class);
		}
	}
	
	/**
	 * 查询某个部门的所有员工的总数
	 * 
	 * @param dept_id
	 * @return
	 */
	public long getMemberListByDeptCount(long dept_id, String query) {
		DBExecuteUtil db = new DBExecuteUtil();
		StringBuffer sbSql = new StringBuffer("select a.ID AS id, a.NAME AS name, case when a.GENDER=1 then '男' else '女' end gender,"
	    		+ " a.IDCARD idcard,a.`CODE` policecode,b.`NAME` dept, a.JOINTIME jointime, c.GROUP_ID AS deptId  from  ecc_contact_info a "
	    		+ "LEFT join ecc_contact_holder c on  a.ID=c.CONTACT_ID LEFT JOIN ecc_contact_group b ON c.GROUP_ID=b.ID where a.STS = 1 ");

		Map<String, Object> params = new HashMap();
		if(dept_id != 0) {
			sbSql.append(" and c.group_id=:dept_id ");
		}
		
		params.put("dept_id", Long.valueOf(dept_id));
		if (StringUtils.isNotEmpty(query)) {
			sbSql.append(" and a.NAME like :query_member or a.IDCARD like :query_member or a.CODE like :query_member");
	        params.put("query_member", "%" + query + "%");
	    }
		
		return this.namedJdbcTemplate.queryForLong(DBUtil.getCountSql(sbSql.toString()), params);
	}
	
	
	/**
	 * 获得用户的roleId
	 * @param name
	 * @return
	 */
	public int getRoleIdByUserId(long userId) {
		Map params = new HashMap();
		String sql = "SELECT ROLE_ID FROM sm_staff_role b WHERE STAFF_ID = :userId";
		params.put("userId", userId);
		return ((Integer) this.db.queryObject(sql, params, Integer.class)).intValue();
	}
	
	
}
